from flask import Flask, Response
from flask import request
import mysql.connector
from flask import jsonify
from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)

# 示例：使用装饰器确定针对哪一个路由进行处理
@app.route("/hello")
def hello_world():
    return "<p>Hello, World!</p>"

#################################################################
# 路由，这里还涉及到了路径参数
@app.route('/user/<username>')
def show_user_profile(username):
    # show the user profile for that user
    return 'User %s' % username


# HTTP method
# 如下是一种指定请求方式为POST的路由设置
@app.route('/method/post1', methods=['POST'])
def hello():
    return '<h1>hi there!</h1>'

# 如下是另一种等价的写法
@app.post('/method/post2')
def hello2():
    return '<h1>hi there, again!</h1>'

#################################################################
# 请求传参
# GET请求
@app.get('/param-transfer/get')
def param_transfer_get():
    # 通过flask库中的request.args来处理GET请求（它类似于dict），通过get(key)的方式获得指定名称的参数
    age = int(request.args.get('age'))
    # 通过指定第二个参数，来使得当前端没有传递name的时候能够获得一个默认值（这里是Unknown）
    name = request.args.get('name', 'Unknown')
    print(name, age)
    return '<h1>Hello, here is your info:name=%s,age=%d!</h1>' % (name, age)

# POST请求
# Form表单形式
@app.post('/param-transfer/post/form')
def param_transfer_post_form():
    # 通过request.form获取通过Form表单传递的参数
    username = request.form['username']
    password = request.form['password']
    print(username, password)
    if username == 'admin' and password == '123456':
        return '<h1>Login success!</h1>'
    else:
        return '<h1>Login failed: Auth failed!</h1>'

# JSON形式
@app.post('/param-transfer/post/json')
def param_transfer_post_json():
    # 通过request.get_json()获得一个Python的dict，里面存储了前端传递的所有参数
    request_body = request.get_json()
    print(request_body)
    username, password = request_body['username'], request_body['password']
    print(username, password)
    if username == 'admin' and password == '123456':
        return '<h1>Login success!</h1>'
    else:
        return '<h1>Login failed: Auth failed!</h1>'

# 上传文件
@app.post('/param-transfer/post/file')
def param_transfer_post_file():
    # request.files是一个dict，存储了前端上传的所有文件，key=文件上传时指定的名称（不一定等于文件名）
    file = request.files['file']
    print(file)
    return '<h1>File uploaded!</h1>'


#################################################################
# 将结果序列化
@app.get('/resp/json')
def get_data():
    data = [{"name": "John", "age": 30, "city": "New York"}, {"name": "Tom"}]
    return jsonify(data)    # 注意 jsonify() 函数返回的是一个 Response 对象


#################################################################
# Python操作数据库（使用原生SQL）
# 查询
@app.get('/database/raw')
def database_raw_query():
    # 创建数据库连接
    conn = mysql.connector.connect(user='root', password='root', host='localhost', database='jrrg')
    # 创建游标
    cursor = conn.cursor()
    # 执行SQL查询
    sql_str = 'SELECT username, password, nickname, phone FROM user;'
    cursor.execute(sql_str)
    # 获取查询结果
    raws = cursor.fetchall()
    # 遍历查询结果并处理
    l = []
    for username, password, nickname, phone in raws:
        l.append({'username': username, 'nickname': nickname, 'phone': phone})
    # 关闭游标和连接
    print(l)
    cursor.close()
    conn.close()
    return jsonify(l)

# 新增
@app.post('/database/raw')
def database_raw_insert():
    # 创建数据库连接
    conn = mysql.connector.connect(user='root', password='root', host='localhost', database='jrrg')
    # 创建游标
    cursor = conn.cursor()
    # 执行SQL查询
    cursor.execute('INSERT INTO user(username, password, nickname, phone) VALUES(%s,%s,%s,%s)', ('root', '123456', 'jerry', '16666666666'))
    conn.commit() # 对于INSERT、UPDATE、DELETE都需要通过commit来提交，否则修改不成功
    cursor.close()
    conn.close()
    return '<h1>Database inserted!</h1>'

@app.put('/database/raw')
def database_raw_update():
    # 创建数据库连接
    conn = mysql.connector.connect(user='root', password='root', host='localhost', database='jrrg')
    # 创建游标
    cursor = conn.cursor()
    # 执行SQL查询
    cursor.execute('UPDATE user set password=%s where username=%s', ('root', 'root'))
    conn.commit() # 对于INSERT、UPDATE、DELETE都需要通过commit来提交，否则修改不成功
    cursor.close()
    conn.close()
    return '<h1>Database updated!</h1>'

@app.delete('/database/raw')
def database_raw_delete():
    # 创建数据库连接
    conn = mysql.connector.connect(user='root', password='root', host='localhost', database='jrrg')
    # 创建游标
    cursor = conn.cursor()
    # 执行SQL查询
    cursor.execute('DELETE FROM user where username=%s', ('root',))
    conn.commit() # 对于INSERT、UPDATE、DELETE都需要通过commit来提交，否则修改不成功
    cursor.close()
    conn.close()
    return '<h1>Database deleted!</h1>'


#################################################################
# ORM

# 配置数据库（这里使用 SQLite，也可以使用 MySQL 或 PostgreSQL）
app.config['SQLALCHEMY_DATABASE_URI'] = 'mysql+mysqlconnector://{user}:{password}@{server}/{database}'.format(
    user='root', password='root', server='localhost', database='jrrg')
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False  # 关闭修改跟踪，提高性能
# 初始化数据库对象
db = SQLAlchemy(app)

# 定义数据模型（须继承自Model）
class User(db.Model):
    __tablename__ = 'user' # 这是实现ORM的关键
    id = db.Column(db.Integer, primary_key=True, autoincrement=True) # 设置主键为int自增
    username = db.Column(db.String(255))
    password = db.Column(db.String(60))
    nickname = db.Column(db.String(255))
    phone = db.Column(db.String(11))

@app.get('/database/orm')
def database_orm_query():
    l = []
    users = db.session.query(User).all()
    for user in users:
        print(user.username, user.password, user.nickname, user.phone)
        l.append({"username": user.username, "nickname": user.nickname, "phone": user.phone})
    print(l)
    return jsonify(l)

@app.post('/database/orm')
def database_orm_insert():
    user = User(username='root', password='123456', nickname='tom', phone='16666666666')
    db.session.add(user)
    db.session.commit() # 同样也需要commit
    return '<h1>Database inserted!</h1>'

@app.put('/database/orm')
def database_orm_update():
    # 先按照条件查询出指定的记录
    user = db.session.query(User).filter(User.username == 'root').first()
    # 修改对应的值
    user.password = 'root'
    # 通过commit来更新数据库
    db.session.commit()
    return '<h1>Database updated!</h1>'

@app.delete('/database/orm')
def database_orm_delete():
    # 同样先找到满足条件的记录
    user = db.session.query(User).filter(User.username == 'root').first()
    # 通过如下方式删除
    db.session.delete(user)
    db.session.commit()
    return '<h1>Database deleted!</h1>'


if __name__ == '__main__':
    app.run(host='localhost', port=8080, debug=True)